﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using WebApiMIT3.Properties;
using WebApiMIT3.Models;


namespace WebApiMIT3.Database
{
    public static class DatabaseConnect
    {
        private static readonly MySqlConnection Connection;
        private static readonly string ConnectionString;
        private static bool isBusyOpeningDatabase;

        static DatabaseConnect()
        {            
            ConnectionString = string.Format("SERVER={0};PORT={1};DATABASE={2};UID={3};PASSWORD={4};", Config.Database_IP, Config.Database_Port, Config.Database_Name, Config.Database_Uid, Config.Database_Pass );
            Connection = new MySqlConnection(ConnectionString);
        }

        public static void Insert(string query)
        {
            try
            {
                if (!OpenConnection())
                {
                    return;
                }

                var cmd = new MySqlCommand(query, Connection);
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw new Exception();
            }
            finally
            {
                CloseConnection();
            }
        }

        /// <summary>
        /// Updates information in the database.
        /// </summary>
        public static List<List<string>> SelectList(string query)
        {
            var result = new List<List<string>>();

            try
            {
                if (!OpenConnection())
                {
                    return new List<List<string>>();
                }

                var cmd = new MySqlCommand();
                cmd.CommandText = query;
                cmd.Connection = Connection;

                StringBuilder builder = new StringBuilder();
                MySqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    List<string> dataModelList = new List<string>();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        dataModelList.Add(reader.GetString(i));
                    }

                    result.Add(dataModelList);
                }

                reader.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw new Exception();
            }
            finally
            {
                CloseConnection();
            }

            return result != null && result.Any()
                ? result
                : new List<List<string>>();
        }

        /// <summary>
        /// Updates information in the database.
        /// </summary>
        public static string Select(string query)
        {
            object queryResult = null;
            try
            {
                if (!OpenConnection())
                {
                    return string.Empty;
                }

                var cmd = new MySqlCommand();
                cmd.CommandText = query;
                cmd.Connection = Connection;
                queryResult = cmd.ExecuteScalar();
            }
            catch (Exception)
            {
                throw new Exception();
            }
            finally
            {
                CloseConnection();
            }

            return queryResult != null
                ? queryResult.ToString()
                : string.Empty;
        }

        /// <summary>
        /// Updates information in the database.
        /// </summary>
        public static void Update(string query)
        {
            try
            {
                if (!OpenConnection())
                {
                    return;
                }

                var cmd = new MySqlCommand();
                cmd.CommandText = query;
                cmd.Connection = Connection;
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw new Exception();
            }
            finally
            {
                CloseConnection();
            }
        }

        /// <summary>
        /// Deletes information in the database.
        /// </summary>
        public static void Delete(string query)
        {
            if (!OpenConnection())
            {
                return;
            }

            var cmd = new MySqlCommand(query, Connection);
            cmd.ExecuteNonQuery();
            CloseConnection();
        }

        /// <summary>
        /// Counts table rows
        /// </summary>
        /// <returns>int</returns>
        public static int Count(string query)
        {
            var count = -1;

            if (!OpenConnection())
            {
                return count;
            }

            var cmd = new MySqlCommand(query, Connection);
            count = int.Parse(cmd.ExecuteScalar().ToString());
            CloseConnection();

            return count;
        }

        /// <summary>
        /// Opens the connection to the database.
        /// </summary>
        /// <returns>bool</returns>
        private static bool OpenConnection()
        {
            var result = false;
            while (isBusyOpeningDatabase)
            {
            }

            isBusyOpeningDatabase = true;
            if (
                Connection.State != ConnectionState.Open
                && Connection.State != ConnectionState.Connecting
                && Connection.State != ConnectionState.Executing
                && Connection.State != ConnectionState.Fetching)
            {
                try
                {
                    Connection.Open();
                    result = true;
                }
                catch (Exception e)
                {
                    Console.WriteLine(e);
                    result = false;
                }
            }

            isBusyOpeningDatabase = false;

            return result;
        }

        /// <summary>
        /// Closes the connection to the database.
        /// </summary>
        /// <returns>bool</returns>
        private static bool CloseConnection()
        {
            if (Connection.State != ConnectionState.Closed)
            {
                try
                {
                    Connection.Close();
                    return true;
                }
                catch (Exception e)
                {
                    Console.WriteLine(e);
                    return false;
                }
            }

            return true;
        }
    }
}